﻿'========================================================================
'功能:群組物件資料庫存取物件
'作者:andy
'日期: 2011/1/28
'========================================================================

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Text
Imports System.Data
Imports MySql.Data.MySqlClient

Namespace OAMS.DAO
    Public Class GroupDAO
        Inherits BaseDAO
        Dim _dbName As String
        Public Sub New(ByVal dbName As String)
            _dbName = dbName
        End Sub

        ''' <summary>
        ''' 取得所有群組資料
        ''' </summary>
        ''' <returns>取得所有群組資料</returns>
        ''' <remarks>取得所有群組資料</remarks>
        Public Function GetGroupIDdt() As DataTable
            ' Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            'Dim sb As New StringBuilder()
            'sb.AppendLine("Select * from GroupID ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("Select * from GroupID ")
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try

        End Function

        ''' <summary>
        ''' 取得所有群組資料及一筆可以新增的紀錄
        ''' </summary>
        ''' <returns>取得所有群組資料及一筆可以新增的紀錄</returns>
        ''' <remarks>取得所有群組資料及一筆可以新增的紀錄</remarks>
        Public Function GetGroupIDInsert() As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}


            'Dim sb As New StringBuilder()
            'sb.AppendLine("Select GroupID,GroupName, 0 as canModi from GroupID union select  '' as GroupID,'' as GroupName ,1 as canModi order by GroupID")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString()).Tables(0)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("Select GroupID,GroupName, 0 as canModi from GroupID union select  '' as GroupID,'' as GroupName ,1 as canModi order by GroupID ")
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try
        End Function


        ''' <summary>
        ''' 取得群組權限的資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="ProgramID"></param>
        ''' <returns>取得群組權限的資料</returns>
        ''' <remarks>取得群組權限的資料</remarks>
        Public Function GetGroupRights(ByVal GroupID As String, ByVal ProgramID As Integer) As DataTable
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                Try
                    sb.AppendLine(" drop   table  AA ")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.ExecuteNonQuery()
                Catch ex As Exception

                End Try
               


                sb.Length = 0
                sb.AppendLine(" create   table  AA ")
                sb.AppendLine("( SeqNo int,DisplayName varchar(100),UseRight BOOLEAN)")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.ExecuteNonQuery()


                sb.Length = 0
                sb.AppendLine("insert into AA")
                sb.AppendLine("select A.SeqNo,A.DisplayName ,B.UseRight from MenuRights  A")
                sb.AppendLine("left join  GroupRights   B on A.ProgramID =B.ProgramID and A.SeqNo=B.SeqNo ")
                sb.AppendLine("where B.GroupID =?GroupID and A.ProgramID =?ProgramID")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                cmd.ExecuteNonQuery()


                sb.Length = 0
                sb.AppendLine("insert into AA")
                sb.AppendLine("select A.SeqNo,A.DisplayName ,0 as UseRight from MenuRights  A")
                sb.AppendLine("where A.ProgramID =?ProgramID  and (not SeqNo  in (Select SeqNo from AA ) )")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                cmd.ExecuteNonQuery()


                sb.Length = 0
                sb.AppendLine("select * from AA   order by SeqNo ")
                cmd = New MySqlCommand(sb.ToString(), con)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                sb.Length = 0
                sb.AppendLine(" drop table AA")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.ExecuteNonQuery()

                Return oTable


            Catch ex As Exception
            Finally
                con.Close()

            End Try

        End Function

        ''' <summary>
        ''' 修改群組權限的資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="ProgramID"></param>
        ''' <param name="SeqNo"></param>
        ''' <param name="UseRight"></param>
        ''' <param name="UID"></param>
        ''' <remarks>修改群組權限的資料</remarks>
        Public Sub UpdateRight(ByVal GroupID As String, ByVal ProgramID As Integer, ByVal SeqNo As Integer, ByVal UseRight As Boolean, ByVal UID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(4) {}

            'sqlParams(0) = New SqlParameter("@ProgramID", SqlDbType.Int)
            'sqlParams(0).Value = ProgramID
            'sqlParams(1) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = GroupID
            'sqlParams(2) = New SqlParameter("@SeqNo", SqlDbType.Int)
            'sqlParams(2).Value = SeqNo
            'sqlParams(3) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(3).Value = UID
            'Dim sb As New StringBuilder()
            'If UseRight Then
            '    sb.AppendLine("delete from GroupRights where ProgramID=@ProgramID and SeqNo=@SeqNo and GroupID=@GroupID")
            '    sb.AppendLine("insert into GroupRights (ProgramID,GroupID,SeqNo,UseRight,CrtUID,CrtDate,ModiUID,ModiDate) values (@ProgramID,@GroupID,@SeqNo,1,@UID,getdate(),'',null)")
            'Else
            '    sb.AppendLine("delete from GroupRights where ProgramID=@ProgramID and SeqNo=@SeqNo and GroupID=@GroupID and( GroupID=@UID or 1=1)")
            'End If

            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                If UseRight Then
                    sb.AppendLine(" delete from GroupRights where ProgramID=?ProgramID and SeqNo=?SeqNo and GroupID=?GroupID ")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                    cmd.Parameters.AddWithValue("?SeqNo", SeqNo)
                    cmd.Parameters.AddWithValue("?GroupID", GroupID)
                    cmd.ExecuteNonQuery()


                    sb.Length = 0
                    sb.AppendLine("insert into GroupRights (ProgramID,GroupID,SeqNo,UseRight,CrtUID,CrtDate,ModiUID,ModiDate) values (?ProgramID,?GroupID,?SeqNo,1,?UID,?adate,'',null)")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                    cmd.Parameters.AddWithValue("?GroupID", GroupID)
                    cmd.Parameters.AddWithValue("?SeqNo", SeqNo)
                    cmd.Parameters.AddWithValue("?UID", UID)
                    cmd.Parameters.AddWithValue("?adate", DateTime.Now)
                    cmd.ExecuteNonQuery()
                Else
                    sb.AppendLine(" delete from GroupRights where ProgramID=?ProgramID and SeqNo=?SeqNo and GroupID=?GroupID  ")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.Parameters.AddWithValue("?ProgramID", ProgramID)
                    cmd.Parameters.AddWithValue("?GroupID", GroupID)
                    cmd.Parameters.AddWithValue("?SeqNo", SeqNo)
                    cmd.ExecuteNonQuery()
                End If
            Catch ex As Exception
            Finally
                con.Close()
            End Try

        End Sub

        ''' <summary>
        ''' 修改或新增一筆資料到群組資料表中
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="GroupName"></param>
        ''' <param name="UID"></param>
        ''' <param name="canModi">true代表是新增</param>
        ''' <remarks>修改或新增一筆資料到群組資料表中</remarks>
        Public Sub UpdateGroupID(ByVal GroupID As String, ByVal GroupName As String, ByVal UID As String, ByVal canModi As Boolean)
            'Dim sqlParams As SqlParameter() = New SqlParameter(3) {}


            ''sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID
            'sqlParams(1) = New SqlParameter("@GroupName", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = GroupName
            'sqlParams(2) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(2).Value = UID
            'Dim sb As New StringBuilder()
            'If canModi Then
            '    sb.AppendLine("insert into GroupID (GroupID,GroupName,CrtUID,CrtDate,ModiUID,ModiDate)values (@GroupID,@GroupName,@UID,GetDate(),'',null )")
            'Else
            '    sb.AppendLine("update GroupID set GroupName=@GroupName ,ModiUID=@UID ,ModiDate=getDate() where GroupID=@GroupID")
            'End If
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                If canModi Then
                    sb.AppendLine(" insert into GroupID (GroupID,GroupName,CrtUID,CrtDate,ModiUID,ModiDate)values (?GroupID,?GroupName,?UID,?aDate,'',null ) ")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.Parameters.AddWithValue("?GroupID", GroupID)
                    cmd.Parameters.AddWithValue("?GroupName", GroupName)
                    cmd.Parameters.AddWithValue("?UID", UID)
                    cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                    cmd.ExecuteNonQuery()
                Else
                    sb.AppendLine("update GroupID set GroupName=?GroupName ,ModiUID=?UID ,ModiDate=?aDate where GroupID=?GroupID ")
                    cmd = New MySqlCommand(sb.ToString(), con)
                    cmd.Parameters.AddWithValue("?GroupName", GroupName)
                    cmd.Parameters.AddWithValue("?UID", UID)
                    cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                    cmd.Parameters.AddWithValue("?GroupID", GroupID)
                    cmd.ExecuteNonQuery()
                End If
            Catch ex As Exception
            Finally
                con.Close()

            End Try

        End Sub


        ''' <summary>
        ''' 檢查某一個群組代號是否存在
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <returns>檢查某一個群組代號是否存在</returns>
        ''' <remarks>檢查某一個群組代號是否存在</remarks>
        Public Function GroupIDIsExist(ByVal GroupID As String) As Boolean
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}
            'sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID

            'Dim sb As New StringBuilder()
            'sb.AppendLine("Select * from GroupID where GroupID=@GroupID")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("Select * from GroupID where GroupID=?GroupID")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)

                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)
                
                Return oTable.Rows.Count > 0


            Catch ex As Exception
            Finally
                con.Close()

            End Try





        End Function
        ''' <summary>
        ''' 檢查人員是否屬於某一群組
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <returns>檢查人員是否屬於某一群組</returns>
        ''' <remarks>檢查人員是否屬於某一群組</remarks>
        Public Function GroupMemberIsExist(ByVal GroupID As String, ByVal UID As String) As Boolean
            'Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            'sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID
            'sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = UID

            'Dim sb As New StringBuilder()
            'sb.AppendLine("Select * from GroupMember where GroupID=@GroupID and UID=@UID ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0).Rows.Count > 0


            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("Select * from GroupMember where GroupID=?GroupID and UID=?UID")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?UID", UID)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                Return oTable.Rows.Count > 0


            Catch ex As Exception
            Finally
                con.Close()

            End Try



        End Function

        ''' <summary>
        ''' 新增群組人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID1"></param>
        ''' <param name="CrtUID"></param>
        ''' <remarks>新增群組人員資料</remarks>
        Public Sub InsertGroupMember(ByVal GroupID As String, ByVal UID1 As String, ByVal CrtUID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(3) {}
            'sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID
            'sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = UID1
            'sqlParams(2) = New SqlParameter("@CrtUID", SqlDbType.NVarChar, 100)
            'sqlParams(2).Value = CrtUID

            'Dim sb As New StringBuilder()
            'sb.AppendLine(" insert into   GroupMember (GroupID,UID,CrtUID,CrtDate,ModiUID,ModiDate) values(@GroupID ,@UID,@CrtUID,getDate(),'',null) ")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()

                sb.AppendLine(" insert into   GroupMember (GroupID,UID,CrtUID,CrtDate,ModiUID,ModiDate) values(?GroupID ,?UID,?CrtUID,?aDate,'',null) ")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?UID", UID1)
                cmd.Parameters.AddWithValue("?CrtUID", CrtUID)
                cmd.Parameters.AddWithValue("?aDate", DateTime.Now)
                cmd.ExecuteNonQuery()
               
            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Sub
        ''' <summary>
        ''' 刪除群組人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <param name="UID"></param>
        ''' <remarks>刪除群組人員資料</remarks>
        Public Sub DeleteGroupMember(ByVal GroupID As String, ByVal UID As String)
            'Dim sqlParams As SqlParameter() = New SqlParameter(2) {}
            'sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID
            'sqlParams(1) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(1).Value = UID

            'Dim sb As New StringBuilder()
            'sb.AppendLine(" delete from  GroupMember  where GroupID=@GroupID and UID=@UID")
            'SqlHelper.ExecuteNonQuery(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()

                sb.AppendLine(" delete from  GroupMember  where GroupID=?GroupID and UID=?UID")
                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                cmd.Parameters.AddWithValue("?UID", UID)
             
                cmd.ExecuteNonQuery()

            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Sub

        ''' <summary>
        ''' 取得個人的所有群組資料
        ''' </summary>
        ''' <param name="UID"></param>
        ''' <returns>取得個人的所有群組資料</returns>
        ''' <remarks>取得個人的所有群組資料</remarks>
        Public Function GetGroupIDByUID(ByVal UID As String) As DataTable
            Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@UID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = UID
            'Dim sb As New StringBuilder()
            'sb.AppendLine("select A.GroupID,B.GroupName ,0 as canModi,1 as canDelete  from GroupMember A left join GroupID B on A.GroupID=B.GroupID where A.UID=@UID  union Select '' as GroupID,'' as GroupName,1 as canModi,0 as canDelete")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("select A.GroupID,B.GroupName ,0 as canModi,1 as canDelete  from GroupMember A left join GroupID B on A.GroupID=B.GroupID where A.UID=?UID  union Select '' as GroupID,'' as GroupName,1 as canModi,0 as canDelete")
                cmd = New MySqlCommand(sb.ToString(), con)

                cmd.Parameters.AddWithValue("?UID", UID)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try


        End Function


        ''' <summary>
        ''' 取得群組的所有人員資料
        ''' </summary>
        ''' <param name="GroupID"></param>
        ''' <returns>取得群組的所有人員資料</returns>
        ''' <remarks>取得群組的所有人員資料</remarks>
        Public Function GetUIDByGroupID(ByVal GroupID As String) As DataTable
            'Dim sqlParams As SqlParameter() = New SqlParameter(1) {}

            'sqlParams(0) = New SqlParameter("@GroupID", SqlDbType.NVarChar, 100)
            'sqlParams(0).Value = GroupID
            'Dim sb As New StringBuilder()
            'sb.AppendLine("select UID as UID1 ,0 as canModi, 1 as canDelete from GroupMember  where GroupID=@GroupID  union select '' as UID1 ,1 as canModi ,0 as canDelete order by UID ")
            'Return SqlHelper.ExecuteDataset(MyConn(_dbName), CommandType.Text, sb.ToString(), sqlParams).Tables(0)
            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()

                Dim sb As New StringBuilder()
                sb.AppendLine("select UID as UID1 ,0 as canModi, 1 as canDelete from GroupMember  where GroupID=?GroupID  union select '' as UID1 ,1 as canModi ,0 as canDelete order by UID1 ")
                cmd = New MySqlCommand(sb.ToString(), con)

                cmd.Parameters.AddWithValue("?GroupID", GroupID)
                Dim oDbAdapter As MySqlDataAdapter
                oDbAdapter = New MySqlDataAdapter(cmd)
                Dim oTable As Data.DataTable
                oTable = New Data.DataTable()
                oDbAdapter.Fill(oTable)

                Return oTable
            Catch ex As Exception
            Finally
                con.Close()

            End Try

        End Function


        Public Sub InsertAllMember()

            Dim cmd As MySqlCommand
            Dim con As MySqlConnection = myMYSqlConn(_dbName)
            Try

                con.Open()
                Dim sb As New StringBuilder()
                sb.AppendLine(" insert into GroupMember  select 'All',emp_no,'Sys',Now(),'',null  from `AUOREF`.emp_data_all  where not emp_no  in  (select UID  from GroupMember where GroupID='All')  ")

                cmd = New MySqlCommand(sb.ToString(), con)
                cmd.ExecuteNonQuery()


            Catch ex As Exception
            Finally
                con.Close()
            End Try

        End Sub

    End Class

End Namespace
